package main

import (
	_ "github.com/Go-SQL-Driver/MySQL"
	"database/sql"
	"log"
	"fmt"
)

//mysql crud

func main() {

	db, err := sql.Open("mysql", "root:root@(localhost:3306)/godb?charset=utf8")
	if err != nil {
		log.Fatalln("db connect wrong")
	}

	defer db.Close()

	stmt, err := db.Prepare("INSERT INTO userinfo values (null, ?, ?, ?)")
	if err != nil {
		log.Fatalln("db Prepare insert wrong")
	}
	result, err := stmt.Exec("astaxie", "RD_DEPT", "2013-12-09")
	if err != nil {
		log.Fatalln("stmt Exec wrong")
	}
	id, err := result.LastInsertId()
	log.Println(id)


	stmt, err = db.Prepare("update userinfo set username = ? where uid = ?")
	if err != nil {
		log.Fatalf("db Prepare update wrong : %v", err)
	}
	result, _ = stmt.Exec("llupdate", id)
	i, _ := result.RowsAffected()
	if i <= 0 {
		log.Fatalln("update failed...")
	} else {
		log.Printf("success recored : %d\n", i)
	}

	rows, _ := db.Query("select * from userinfo")

	for rows.Next() {
		var Uid int
		var userName string
		var deptName string
		var created string
		rows.Scan(&Uid, &userName, &created, &deptName)
		fmt.Println(Uid, userName, deptName, created)
	}

	stmt, _ = db.Prepare("delete from userinfo where uid = ?")
	result, _ = stmt.Exec(16)

	i, _ = result.RowsAffected()
	if i <= 0 {
		log.Fatalln("no delete ...")
	} else {
		log.Printf("success delete recored : %d\n", i)
	}

}

/*

CREATE TABLE `userinfo` (
  `uid` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) DEFAULT NULL,
  `departname` varchar(64) DEFAULT NULL,
  `created` date DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

*/